oracle函数之case和decode的用法区别及性能比较 | 您所在的位置:网站首页 › decode函数和case when区别 › oracle函数之case和decode的用法区别及性能比较 |
在oracle世界,你可以使用: 1)case表达式 或者 2)decode函数 来实现逻辑判断。Oracle的DECODE函数功能很强,灵活运用的话可以避免多次扫描,从而提高查询的性能。而CASE是9i以后提供的语法,这个语法更加的灵活,提供了IF THEN ELSE的功能。 case表达式 case表达式,可分两种,简单和搜索,简单case后接表达式,如: 对于简单的case需要几点注意: 1)寻找when的优先级:从上到下 2)再多的when,也只有一个出口,即其中有一个满足了expr就马上退出case 3)不能把return_expr和else_expr指定为null,而且,expr、comparison_expr和return_expr的数据类型必须相同。 搜索case: CASE WHEN condition THEN return_expr [WHEN condition THEN return_expr] ... ELSE else_expr END 例子: [sql] view plaincopySELECT (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999' WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999' WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999' WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000' END) AS BUCKET, COUNT(*) AS Count_in_Group FROM customers WHERE cust_city = 'Marshal' GROUP BY (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999' WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999' WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999' WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000' END); BUCKET COUNT_IN_GROUP ------------- -------------- 0 - 3999 8 4000 - 7999 7 8000 - 11999 7 12000 - 16000 1用decode可以违反第3NF(行不可再分,列不可再分,列不可重复):列重复 [sql] view plaincopyhr@ORCL> select * from a; ID NAME ---------- ---------- 1 a 2 b 3 c 1 a hr@ORCL> select sum(decode(id,1,1,0)) think, 2 sum(decode(id,2,2,0)) water, 3 sum(decode(id,3,3,0)) linshuibin 4 from a; THINK WATER LINSHUIBIN ---------- ---------- ---------- 2 2 3
一个字段,decode函数可以完全改写简单case; 多个字段,需要复杂的case,方可。 语法: DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的结果返then1,...,如果不等于任何一个if值,则返回else。可以用函数或表达式来替代value,if,then,else从而作出一些更有用的比较。 来看看具体的运用: 1 假设我们想给百度职员加工资,其标准是:工资在8000元以下的将加20%;工资在8000元以上的加15% 则: select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary) "revised_salary" from employee 2 表table_subject,有subject_name列。要求按照:语、数、外的顺序进行排序 则: select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3) decode和简单case的性能比较
Oracle的DECODE函数功能很强,灵活运用的话可以避免多次扫描,从而提高查询的性能。而CASE是9i以后提供的语法,这个语法更加的灵活,提供了IF THEN ELSE的功能。 对于很多情况,DECODE和CASE都能解决问题,个人更倾向于使用DECODE,一方面是从8i保留下来的习惯,另一方面是DECODE的语法更加的简洁,代码量要小一些。 不过今天在看Oracle9i的数据仓库手册时发现,Oracle在文档中提到CASE语句的效率会更高一些,尤其是CASE表达式 WHEN 常量 THEN的语法,效率要比CASE WHEN表达式 THEN的语法更高一些。对于后面这种说法倒是没有太多的疑问,对于CASE比DECODE效率高这种说法倒是第一次看到,印象中DECODE效率很高,应该不会比CASE的效率差。 到底效率如何,还是要具体的实例来说: SQL> CREATE TABLE T AS2 SELECT A.*3 FROM DBA_OBJECTS A, DBA_MVIEWS; Table created. SQL> SELECT COUNT(*) FROM T; COUNT(*)----------6075760 下面检查DECODE和两种CASE语句的效率: SQL> SET ARRAY 1000SQL> SET TIMING ONSQL> SET AUTOT TRACE SQL> SELECT DECODE(OWNER, 'SYSTEM', 'SYSTEM', 'SYS', 'SYSTEM', 'USER') 2 FROM T; 6075760 rows selected. Elapsed: 00:00:07.24 Execution Plan----------------------------------------------------------Plan hash value: 1601196873 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 || 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |-------------------------------------------------------------------------- Note------ dynamic sampling used for this statement Statistics----------------------------------------------------------0 recursive calls0 db block gets47551 consistent gets0 physical reads0 redo size46288564 bytes sent via SQL*Net to client67317 bytes received via SQL*Net from client6077 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)6075760 rows processed SQL> SELECT CASE OWNER WHEN 'SYSTEM' THEN 'SYSTEM'2 WHEN 'SYS' THEN 'SYSTEM' 3 ELSE 'USER' END 4 FROM T; 6075760 rows selected. Elapsed: 00:00:07.22 Execution Plan----------------------------------------------------------Plan hash value: 1601196873 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 || 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |-------------------------------------------------------------------------- Note------ dynamic sampling used for this statement Statistics----------------------------------------------------------0 recursive calls0 db block gets47551 consistent gets0 physical reads0 redo size46288578 bytes sent via SQL*Net to client67317 bytes received via SQL*Net from client6077 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)6075760 rows processed SQL> SELECT CASE WHEN OWNER = 'SYSTEM' THEN 'SYSTEM'2 WHEN OWNER = 'SYS' THEN 'SYSTEM' 3 ELSE 'USER' END 4 FROM T; 6075760 rows selected. Elapsed: 00:00:07.23 Execution Plan----------------------------------------------------------Plan hash value: 1601196873 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 || 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |-------------------------------------------------------------------------- Note------ dynamic sampling used for this statement Statistics----------------------------------------------------------0 recursive calls0 db block gets47551 consistent gets0 physical reads0 redo size46288585 bytes sent via SQL*Net to client67317 bytes received via SQL*Net from client6077 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)6075760 rows processed |
CopyRight 2018-2019 实验室设备网 版权所有 |